/**
 * 
 */
package com.sq.partner.manager.util;

import java.io.Reader;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.sq.partner.manager.common.Constants;

/**
 * @author X.Y.CHEN
 *
 */
public class SqlUtil {
    private static Logger logger = LogUtil.getLog(SqlUtil.class);
	/***
	 * 拼接in内容超过1000的情况
	 * @param list
	 * @param columnName
	 * @return
	 */
	public static String getInStr(List<?> list, String columnName ){
		StringBuffer result = new StringBuffer();
		for (int i = 0; i < list.size(); i++) {
			if ((i % 1000) == 0 && i > 0) {
				result.deleteCharAt(result.length() - 1);
				result.append(") OR  " + columnName+"  IN ( '" + list.get(i) + "',");
			} else {
				result.append("'" + list.get(i) + "',");
			}
		}
		return result.deleteCharAt(result.length() - 1).toString();
	}

    public static long getSeqNextVal(Connection conn,String seqName) throws SQLException {
        String sql = "select "+seqName+".nextVal from dual";
        Statement stmt = null;
        ResultSet rs = null;
        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            rs.next();
            return rs.getLong(1);
        }finally {
            close(stmt,rs);
        }
    }

    public static void close(Connection conn){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                logger.warn("关闭connection失败："+ e );
            }
        }
    }

    public static void close(Statement stmt){
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                logger.warn("关闭Statement失败："+ e );
            }
        }
    }

    public static void close(Statement stmt,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                logger.warn("关闭ResultSet失败："+ e );
            }
        }

        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                logger.warn("关闭Statement失败："+ e );
            }
        }
    }

    public static final Class BigDecimalClass = BigDecimal.class;
    public static final Class BlobClass = Blob.class;
    public static final Class BooleanClass = Boolean.class;
    public static final Class booleanClass = boolean.class;
    public static final Class ByteClass = Byte.class;
    public static final Class byteClass = byte.class;
    public static final Class ClobClass = Clob.class;
    public static final Class DateClass = Date.class;
    public static final Class SqlDateClass = java.sql.Date.class;
    public static final Class DoubleClass = Double.class;
    public static final Class doubleClass = double.class;
    public static final Class FloatClass = Float.class;
    public static final Class floatClass = float.class;
    public static final Class IntegerClass = Integer.class;
    public static final Class intClass = int.class;
    public static final Class LongClass = Long.class;
    public static final Class longClass = long.class;
    public static final Class ObjectClass = Object.class;
    public static final Class ShortClass = Short.class;
    public static final Class shortClass = short.class;
    public static final Class stringClass = String.class;
    public static final Class Timestamp = Timestamp.class;
    /**
     * 调用公共set方法将rs的值放到bean里面
     * 可以匹配 驼峰标志和带下划线的格式(带下划线的全部转为大写)
     * 如 setBeanName 会去map中取beanName和BEAN_NAME值
     * setBEAN_NAME 会去 map中取 BEAN_NAME和 beanName值
     * 建议Map 使用DBMap
     * @param <T>
     * @param clz
     * @param map
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws IllegalArgumentException
     * @throws Exception
     */
    public static <T>   T transRSMap2Bean(Class<T> clz,Map map) throws Exception {
        T obj = clz.newInstance();
        Method[] ms = clz.getMethods();//获取所有公共方法
        for(Method m : ms){
            String name = m.getName();
            Class[] paraTypes = m.getParameterTypes();
            if(name.startsWith("set") && paraTypes.length == 1){
                Class paraClz = paraTypes[0];
                String prop = name.replaceFirst("set","");
                if(prop.indexOf("_") == -1){//setUserName
                    prop = prop.substring(0,1).toLowerCase()+prop.substring(1);//userName
                }else{//setUSER_NAME
                    prop = prop.toUpperCase();//USERNAME
                }

                Object val = getValue(paraClz,map,prop.toUpperCase());
                if(val == null){
                    String propTrans = transStr(prop);
                    val = getValue(paraClz,map,propTrans.toUpperCase());

                }

                if(val != null){
                    m.invoke(obj,new Object[]{val});
                }
            }
        }

        return obj;
    }

    /**
     * 获取
     * @param clz
     * @param rsMap resultMeteDateMap or Hibernate ALIAS_TO_ENTITY_MAP
     * @param name
     * @return
     * @throws Exception
     */
    private static Object getValue(Class clz,Map rsMap,String name) throws Exception{
        Object val = rsMap.get(name);
        if(val != null){
            if (SqlDateClass.equals(clz) || BigDecimalClass.equals(clz) || ClobClass.equals(clz) ||  ObjectClass.equals(clz)){
                return val;
            }else if (stringClass.equals(clz)){
                if(val instanceof Clob){
                    Clob c = (Clob)val;
                    //GBK
                    StringBuffer sb = new StringBuffer();
                    long length = c.length();
                    int arrayLength = 612;
                    //初使化每次读取长度
                    int l = length > arrayLength?arrayLength:(int)length;
                    Reader read = c.getCharacterStream();
                    char[] cc = new char[l];
                    while (read.read(cc) != -1){
                        sb.append(new String(cc));
                        length -= arrayLength;
                        //最后一次不要多读
                        if(length < arrayLength && length > 0){
                            cc = new char[(int)length];
                        }
                    }
                    read.close();
                    return  sb.toString();
                }
                return val.toString();
            }else if (LongClass.equals(clz) || longClass.equals(clz)){
                return ((BigDecimal)val).longValue();
            }else if (DoubleClass.equals(clz) || doubleClass.equals(clz)){
                return ((BigDecimal)val).doubleValue();
            }else if (DateClass.equals(clz)){
                return new Date(((java.sql.Date)val).getTime());
            }else if (Timestamp.equals(clz)){
                if(val instanceof Timestamp){
                    return val;
                }else{
                    return new Timestamp(((java.sql.Date)val).getTime());
                }
            }else if (IntegerClass.equals(clz) || intClass.equals(clz)){
                return ((BigDecimal)val).intValue();
            }else if (BlobClass.equals(clz)){
                return val;
            }else if (BooleanClass.equals(clz) || booleanClass.equals(clz)){
                //暂不支持
                throw new IllegalArgumentException("未处理的数据类型："+clz.getName()+"请在SQLUtil中添加");
            }else if (ByteClass.equals(clz) || byteClass.equals(clz)){
                //暂不支持
                throw new IllegalArgumentException("未处理的数据类型："+clz.getName()+"请在SQLUtil中添加");
            }else if (FloatClass.equals(clz) || floatClass.equals(clz)){
                return ((BigDecimal)val).floatValue();
            }else if (ShortClass.equals(clz) || shortClass.equals(clz)){
                //暂不支持
                throw new IllegalArgumentException("未处理的数据类型："+clz.getName()+"请在SQLUtil中添加");
                //return ((BigDecimal)val).shortValue();
            }else{
                throw new IllegalArgumentException("未处理的数据类型："+clz.getName()+"请在SQLUtil中添加");
            }
        }
        return val;
    }

    /**
     * 将带下划线的转成驼峰标志，如果是驼峰标志的，则转成全部大写的下划线标志
     * @param s
     * @return
     */
    private  final static String transStr(String s){
        if(s == null || "".equals(s)){
            return s;
        }

        if(s.indexOf("_") == -1){
            if(s.matches("^[^A-Z]+$")){
                return s;
            }else{
                s = s.substring(0,1) + s.substring(1).replaceAll("[A-Z]","_$0");
                s = s.toUpperCase();
                return s;
            }

        }else{
            String resut = "";
            String[] sArray = s.toLowerCase().split("_");
            for(int i = 0; i < sArray.length;i++){
                String st = sArray[i];
                if(i == 0){
                    resut += st;
                }else{
                    resut += st.substring(0,1).toUpperCase() + st.substring(1);
                }
            }
            return resut;
        }

    }
    
    public StringBuilder buildPageSqlForOracle(String sql, Page page) {  
        StringBuilder pageSql = new StringBuilder(100);  
        String beginrow = String.valueOf((page.getCurrent() - 1) * page.getSize()); 
        String endrow = String.valueOf(page.getCurrent() * page.getSize());  
        pageSql.append("select * from ( select temp.*, rownum row_id from ( ");  
        pageSql.append(sql);  
        pageSql.append(" ) temp where rownum <= ").append(endrow);  
        pageSql.append(") where row_id > ").append(beginrow);
        return pageSql;  
    }  
    
    public static Map<String,Object> buildPageMap(Page<?> page) {  
    	Integer beginrow = Integer.valueOf((page.getCurrent() - 1) * page.getSize()); 
		Integer endrow = Integer.valueOf(page.getCurrent() * page.getSize());
	    Map<String,Object> map=new HashMap<String,Object>();
		map.put(Constants.BEGINROW, beginrow);
		map.put(Constants.ENDROW, endrow);
        return map;  
    }
}
